﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using ControleRepresentante.Data;
using ControleRepresentante.Migration.Models;

namespace ControleRepresentante.Migration.Repositories
{
    internal class ZTRepository : IDisposable
    {
        private readonly DataAccess m_DataAccess;

        public ZTRepository()
        {
            OleDbConnection oleDbConnection = new OleDbConnection(ConfigurationManager.ConnectionStrings["ZT"].ConnectionString);
            oleDbConnection.Open();
            m_DataAccess = new DataAccess(oleDbConnection);
        }

        public IEnumerable<ZTFabrica> GetFabricas()
        {
            using (IDataReader reader = m_DataAccess.ExecuteReader(@"SELECT * FROM Fabrica"))
            {
                while (reader.Read())
                {
                    yield return new ZTFabrica
                                     {
                                         Bairro = (string)reader["Bairro"],
                                         CEP = (string)reader["CEP"],
                                         Cidade = (string)reader["Cidade"],
                                         CNPJ = (string)reader["CNPJ"],
                                         Contato = (string)reader["Contato"],
                                         Email = (string)reader["Email"],
                                         Endereco = (string)reader["Endereco"],
                                         Estado = (string)reader["Estado"],
                                         Fax = (string)reader["Fax"],
                                         Id = (int)reader["Id"],
                                         InscricaoEstadual = (string)reader["InscricaoEstadual"],
                                         Observacao = (string)reader["Observacao"],
                                         RazaoSocial = (string)reader["RazaoSocial"],
                                         Telefone = (string)reader["Telefone"]
                                     };
                }
            }
        }

        public void Dispose()
        {
            m_DataAccess.Dispose();
        }

        public IEnumerable<ZTTransportadora> GetTransportadoras()
        {
            using (IDataReader reader = m_DataAccess.ExecuteReader("SELECT * FROM Transportadora"))
            {
                while (reader.Read())
                {
                    yield return new ZTTransportadora
                                     {
                                         Id = (int)reader["ID"],
                                         RazaoSocial = (string)reader["RazaoSocial"],
                                         Telefone = reader["Telefone"] == DBNull.Value ? null : (string)reader["Telefone"]
                                     };
                }
            }
        }

        public IEnumerable<ZTVendedor> GetVendedores()
        {
            using (IDataReader reader = m_DataAccess.ExecuteReader("SELECT * FROM Vendedor"))
            {
                while (reader.Read())
                {
                    yield return new ZTVendedor
                                     {
                                         Id = (int)reader["ID"],
                                         Nome = (string)reader["Nome"],
                                         Sigla = (string)reader["Sigla"],
                                         Endereco = (string)reader["Endereco"],
                                         Bairro = (string)reader["Bairro"],
                                         CEP = (string)reader["CEP"],
                                         Cidade = (string)reader["Cidade"],
                                         Estado = (string)reader["Estado"],
                                         Telefone = (string)reader["Telefone"],
                                         Celular = (string)reader["Celular"],
                                         Fax = (string)reader["Fax"],
                                         Email = (string)reader["Email"],
                                         CNPJ = (string)reader["CNPJ"],
                                         InscricaoEstadual = (string)reader["InscricaoEstadual"],
                                         Banco = (string)reader["Banco"],
                                         Agencia = (string)reader["Agencia"],
                                         Conta = (string)reader["Conta"],
                                         Observacao = (string)reader["Observacao"],
                                     };
                }
            }
        }

        public IEnumerable<ZTConta> GetContas()
        {
            using (IDataReader reader = m_DataAccess.ExecuteReader("SELECT * FROM Conta"))
            {
                while (reader.Read())
                {
                    yield return new ZTConta
                                     {
                                         Agencia = (string)reader["Agencia"],
                                         Banco = (string)reader["Banco"],
                                         Conta = (string)reader["Conta"],
                                         IdFabrica = (int)reader["IdFabrica"],
                                     };
                }
            }
        }

        public IEnumerable<ZTCliente> GetClientes()
        {
            using (IDataReader reader = m_DataAccess.ExecuteReader("SELECT * FROM Cliente"))
            {
                while (reader.Read())
                {
                    yield return new ZTCliente
                                     {
                                         Bairro = (string)reader["Bairro"],
                                         BairroCobranca = (string)reader["BairroCobranca"],
                                         BairroEntrega = (string)reader["BairroEntrega"],
                                         CEP = (string)reader["CEP"],
                                         CEPCobranca = (string)reader["CEPCobranca"],
                                         CEPEntrega = reader["CEPEntrega"] == DBNull.Value ? null : (string)reader["CEPEntrega"],
                                         Cidade = (string)reader["Cidade"],
                                         CidadeCobranca = (string)reader["CidadeCobranca"],
                                         CidadeEntrega = reader["CidadeEntrega"] == DBNull.Value ? String.Empty : (string)reader["CidadeEntrega"],
                                         CNPJ = (string)reader["CNPJ"],
                                         CNPJCobranca = (string)reader["CNPJCobranca"],
                                         CNPJEntrega = (string)reader["CNPJEntrega"],
                                         CodigoJolitex = (string)reader["CodigoJolitex"],
                                         Contato = (string)reader["Contato"],
                                         Email = (string)reader["Email"],
                                         Endereco = (string)reader["Endereco"],
                                         EnderecoCobranca = (string)reader["EnderecoCobranca"],
                                         EnderecoEntrega = (string)reader["EnderecoEntrega"],
                                         Estado = (string)reader["Estado"],
                                         EstadoCobranca = (string)reader["EstadoCobranca"],
                                         EstadoEntrega = reader["EstadoEntrega"] == DBNull.Value ? String.Empty : (string)reader["EstadoEntrega"],
                                         Fax = (string)reader["Fax"],
                                         FaxCobranca = (string)reader["FaxCobranca"],
                                         FaxEntrega = (string)reader["FaxEntrega"],
                                         Id = (int)reader["Id"],
                                         IdVendedor = (int)reader["IdVendedor"],
                                         InscricaoEstadual = (string)reader["InscricaoEstadual"],
                                         InscricaoEstadualCobranca = (string)reader["InscricaoEstadualCobranca"],
                                         InscricaoEstadualEntrega = (string)reader["InscricaoEstadualEntrega"],
                                         Observacao = (string)reader["Observacao"],
                                         RazaoSocial = (string)reader["RazaoSocial"],
                                         Regiao = reader["Regiao"] == DBNull.Value ? (int?) null : Convert.ToInt32(reader["Regiao"]),
                                         Telefone = (string)reader["Telefone"],
                                         TelefoneCobranca = (string)reader["TelefoneCobranca"],
                                         TelefoneEntrega = (string)reader["TelefoneEntrega"],
                                     };
                }
            }
        }

        public IEnumerable<ZTPedido> GetPedidos()
        {
            using (IDataReader reader = m_DataAccess.ExecuteReader(@"
                SELECT ID, IDCliente, IDVendedor, IDFabrica, IDTransportadora,
                       CodigoJolitex, Sincronizado, Data, CondicaoPagamento,
                       PrazoEntrega, Observacao, Desconto, Faturado, Cancelado,
                       Total, Pecas, ObservacaoBordados,
                       (
                            SELECT SUM(Quantidade * PrecoUnitario)
                            FROM Produto
                            WHERE IDPedido = ID
                       ) AS CalculatedTotal
                FROM Pedido"))
            {
                while (reader.Read())
                {
                    int codigoJolitex = (int)reader["CodigoJolitex"];
                    yield return new ZTPedido
                    {
                        CodigoJolitex = codigoJolitex == 0 ? (int?)null : codigoJolitex,
                        CondicaoPagamento = (string)reader["CondicaoPagamento"],
                        Data = (DateTime)reader["Data"],
                        Id = (int)reader["Id"],
                        IdCliente = (int)reader["IdCliente"],
                        IdFabrica = (int)reader["IdFabrica"],
                        IdTransportadora = (int)reader["IdTransportadora"],
                        IdVendedor = (int)reader["IdVendedor"],
                        Observacao = (string)reader["Observacao"],
                        PrazoEntrega = (string)reader["PrazoEntrega"],
                        Sincronizado = (bool)reader["Sincronizado"],
                        Desconto = reader["Desconto"] == DBNull.Value ? null : (string)reader["Desconto"],
                        ObservacaoBordados = reader["ObservacaoBordados"] == DBNull.Value ? null : (string)reader["ObservacaoBordados"],
                        CalculatedTotal = (double)reader["CalculatedTotal"],
                        Total = (double)reader["Total"],
                        Faturado = (bool)reader["Faturado"],
                        Cancelado = (bool)reader["Cancelado"],
                    };
                }
            }
        }

        public IEnumerable<ZTProduto> GetProdutos()
        {
            using (IDataReader reader = m_DataAccess.ExecuteReader("SELECT * FROM Produto"))
            {
                while (reader.Read())
                {
                    yield return new ZTProduto
                                     {
                                         Codigo = (string)reader["Codigo"],
                                         Descricao = (string)reader["Descricao"],
                                         IdPedido = (int)reader["IdPedido"],
                                         PrecoUnitario = (double)reader["PrecoUnitario"],
                                         Quantidade = (int)reader["Quantidade"],
                                     };
                }
            }
        }

        public int GetTotalFabricas()
        {
            return GetTotalFromTable("Fabrica");
        }

        private int GetTotalFromTable(string tableName)
        {
            return (int) m_DataAccess.ExecuteScalar(string.Format("SELECT COUNT(*) FROM {0}", tableName));
        }

        public int GetTotalProdutos()
        {
            return GetTotalFromTable("Produto");
        }

        public int GetTotalPedidos()
        {
            return GetTotalFromTable("Pedido");
        }

        public int GetTotalClientes()
        {
            return GetTotalFromTable("Cliente");
        }

        public int GetTotalContas()
        {
            return GetTotalFromTable("Conta");
        }

        public int GetTotalVendedores()
        {
            return GetTotalFromTable("Fabrica");
        }

        public int GetTotalTransportadoras()
        {
            return GetTotalFromTable("Fabrica");
        }
    }
}